package com.sp2p.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;

import com.shove.data.DataException;
import com.shove.data.DataRow;
import com.shove.data.DataSet;
import com.shove.data.dao.Database;
import com.shove.data.dao.MySQL;
import com.shove.security.Encrypt;
import com.shove.util.BeanMapUtils;
import com.shove.util.UtilDate;
import com.sp2p.constants.IConstants;
import com.sp2p.database.Dao;
import com.sp2p.database.Dao.Views;
import com.sp2p.database.Dao.Views.v_t_chianpnr_repay;
import com.sp2p.util.DateUtil;

/**
 * @ClassName: FinanceDao.java
 * @Author: gang.lv
 * @Date: 2013-3-4 上午11:15:29
 * @Copyright: 2013 www.emis.com Inc. All rights reserved.
 * @Version: V1.0.1
 * @Descrb: 理财数据处理层
 */
public class FinanceDao {
	/**
	 * @Return: Map<String, String>
	 * @Descb: 查询余额宝处理中的投资赎回的债权
	 * @Throws: SQLException, DataException
	 */
	public Map<String, String> queryYebDebt(Connection conn, long pid,long userId,double amount)
			throws SQLException, DataException {
		StringBuffer condition = new StringBuffer();
		condition.append(" productId = " + pid);
		condition.append(" and alienatorId <> " + userId);
		condition.append(" and debtSum < " + amount);
		Dao.Views.v_t_yeb_assignment_debt v_t_yeb_assignment_debt = new Dao().new Views().new v_t_yeb_assignment_debt();
		DataSet dataSet = v_t_yeb_assignment_debt.open(conn, " * ", condition.toString(), " order by applyTime asc,debtSum desc ", 0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	/**
	 * @MethodName: queryBorrowList
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-4 上午11:13:28
	 * @Return: Map<String, String>
	 * @Descb: 查询借款列表
	 * @Throws: SQLException, DataException
	 */
	public List<Map<String, Object>> queryBorrowList(Connection conn,
			int borrowMode, int limitStart, int limitCount)
			throws SQLException, DataException {
		Dao.Views.v_t_borrow_list borrowList = new Dao().new Views().new v_t_borrow_list();
		DataSet dataSet = borrowList.open(conn, "*", "", "", limitStart,
				limitCount);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	
	/**
	 * @MethodName: queryBorrowDetailById
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午06:04:15
	 * @Return:
	 * @Descb: 根据ID查询借款的详情
	 * @Throws:
	 */
	public Map<String, String> queryDebtDetailById(Connection conn, long id)
			throws SQLException, DataException {
		Dao.Views.v_t_assignment_debt v_t_assignment_debt = new Dao().new Views().new v_t_assignment_debt();
		DataSet dataSet = v_t_assignment_debt.open(conn, " * ", " id = " + id, "", 0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**
	 * @Return:
	 * @Descb:根据ID、product、isForNewUser查询借款详细信息
	 * @Throws:
	 */
	public Map<String, String> queryBorrowDetail(Connection conn, long id,int borrowProduct,int isForNewUser)
			throws SQLException, DataException {
		StringBuffer condition = new StringBuffer();
		condition.append(" id = " + id);
		condition.append(" and borrowProduct = " + borrowProduct);
		condition.append(" and isForNewUser = " + isForNewUser);
		Dao.Views.v_t_borrow_detail borrowDetail = new Dao().new Views().new v_t_borrow_detail();
		DataSet dataSet = borrowDetail.open(conn, " * ", condition.toString(), "", 0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**
	 * @Return:
	 * @Descb:根据product、isForNewUser查询借款详细信息(根据投资期限，收益利率，可投资筛选)
	 * @Throws:
	 */
	public List<Map<String, Object>>  queryBorrowList(Connection  conn) throws SQLException, DataException{
		String sql = "SELECT * FROM (SELECT * FROM  v_t_borrow_detail WHERE  borrowProduct = 4  AND isForNewUser = 0  AND borrowStatus = 2  ORDER BY  annualRate DESC) t GROUP BY  deadline ";
		DataSet dataSet = MySQL.executeQuery(conn, sql);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	
	/**
	 * @Return:
	 * @Descb:查询债权详细信息(根据投资期限，收益利率，可投资筛选)
	 * @Throws:
	 */
	public List<Map<String, Object>>  queryDebtList(Connection  conn,Long alienatorId) throws SQLException, DataException{
		String sql = "SELECT * FROM (SELECT * FROM `v_t_assignment_debt`  WHERE debtStatus = 2 AND alienatorId <>"+alienatorId+" ORDER BY realAnnualRate DESC) t GROUP BY deadline-hasDeadline ";
		DataSet dataSet = MySQL.executeQuery(conn, sql);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	

	/**
	 * @MethodName: queryBorrowDetailById
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午06:04:15
	 * @Return:
	 * @Descb: 根据ID查询借款的详情
	 * @Throws:
	 */
	public Map<String, String> queryBorrowDetailById(Connection conn, long id)
			throws SQLException, DataException {
		Dao.Views.v_t_borrow_detail borrowDetail = new Dao().new Views().new v_t_borrow_detail();
		DataSet dataSet = borrowDetail.open(conn, " * ", " id=" + id, "", 0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	/**
	 * @MethodName: queryBorrowDetailById
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午06:04:15
	 * @Return:
	 * @Descb: 根据ID查询借款产品的详情
	 * @Throws:
	 */
	public Map<String, String> queryProductDetailById(Connection conn, long id,int borrowProduct)
			throws SQLException, DataException {
		StringBuffer condition = new StringBuffer();
		condition.append(" pid = " + id);
		condition.append(" and type = " + borrowProduct);
		Dao.Views.v_t_product productDetail = new Dao().new Views().new v_t_product();
		DataSet dataSet = productDetail.open(conn, " * ", condition.toString(), "", 0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	//查询余额宝产品累计总额度
	public Map<String, String> queryYebTotalYd(Connection conn, int borrowProduct)
		throws SQLException, DataException {
		StringBuffer condition = new StringBuffer();
		condition.append(" type = " + borrowProduct);
		Dao.Views.v_t_product productDetail = new Dao().new Views().new v_t_product();
		DataSet dataSet = productDetail.open(conn, " sum(totalsum) as totalSumAll ", condition.toString(), "", 0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public Map<String, String> queryYebAnnualRate(Connection conn)
			throws SQLException, DataException {
		StringBuffer command = new StringBuffer();
		command.append(" select annualRate from t_yeb_annualrate where status = 1 limit 0,1 ");
		DataSet ds = MySQL.executeQuery(conn, command.toString());
		return BeanMapUtils.dataSetToMap(ds);
	}
	
	/***
	 * 根据Id查询借款信息
	 * @param conn
	 * @param id
	 * @return
	 * @throws SQLException
	 * @throws DataException
	 */
	public Map<String, String> queryEveryAmountById(Connection conn, long id)
		throws SQLException, DataException {
	Dao.Views.t_borrow_success_list borrowlist = new Dao().new Views().new t_borrow_success_list();
	DataSet dataSet = borrowlist.open(conn, " * ", " id=" + id, "", 0, 1);
	return BeanMapUtils.dataSetToMap(dataSet);
	}

	/**
	 * @MethodName: queryUserInfoById
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午06:04:54
	 * @Return:
	 * @Descb: 根据ID查询借款信息发布者个人信息
	 * @Throws:
	 */
	public Map<String, String> queryUserInfoById(Connection conn, long id)
			throws SQLException, DataException {
		Dao.Views.v_t_borrow_user_info v_t_borrow_user_info = new Dao().new Views().new v_t_borrow_user_info();
		DataSet dataSet = v_t_borrow_user_info.open(conn, "*", " id='" + id+"'",
				"", 0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	/**
	 * @MethodName: queryUserIdentifiedByid
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:00:04
	 * @Return:
	 * @Descb: 根据ID查询用户认证信息
	 * @Throws:
	 */
	public List<Map<String, Object>> queryUserIdentifiedByid(Connection conn,
			long id) throws SQLException, DataException {
		Dao.Views.v_t_borrow_user_materialsauth user_materialsauth = new Dao().new Views().new v_t_borrow_user_materialsauth();
		DataSet dataSet = user_materialsauth.open(conn, " * ,count(DISTINCT materAuthTypeId)  as testnum ", " id=" + id+" group by materAuthTypeId ", " ",
				-1, -1);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}

	/**
	 * @MethodName: queryPaymentByid
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:03:01
	 * @Return:
	 * @Descb: 根据ID查询本期还款信息
	 * @Throws:
	 */
	public List<Map<String, Object>> queryRePayByid(Connection conn, long id)
			throws SQLException, DataException {
		Dao.Views.v_t_borrow_repayment v_t_borrow_repayment = new Dao().new Views().new v_t_borrow_repayment();
		DataSet dataSet = v_t_borrow_repayment.open(conn, " * ", " borrowId=" + id, "",
				-1, -1);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}

	/**
	 * @MethodName: queryCollectionByid
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:04:28
	 * @Return:
	 * @Descb: 根据ID查询本期催收信息
	 * @Throws:
	 */
	public List<Map<String, Object>> queryCollectionByid(Connection conn,
			long id) throws SQLException, DataException {
		Dao.Views.v_t_borrow_collection collection = new Dao().new Views().new v_t_borrow_collection();
		DataSet dataSet = collection.open(conn, " * ", " borrowId=" + id, "",
				-1, -1);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}

	/**
	 * @MethodName: queryInvestByid
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:06:00
	 * @Return:
	 * @Descb: 根据ID查询投资记录
	 * @Throws:
	 */
	public List<Map<String, Object>> queryInvestByid(Connection conn, long id)
			throws SQLException, DataException {
		Dao.Views.v_t_borrow_investrecord investrecord = new Dao().new Views().new v_t_borrow_investrecord();
		DataSet dataSet = investrecord.open(conn, "  id ,borrowId , f_formatting_username(username) as username , investAmount ,invAmount, investTime , investor , isDebt ,oriInvestor, oriInvestorUsername,deadline,recievedInterest,recivedPrincipal,username as fullname ", " borrowId=" + id,
				//" id desc", -1, -1);//modify by houli 按照时间的正序排
				" investTime asc", -1, -1);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	

	
	/**
	 * @MethodName: getInvestStatus
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-11 下午06:47:23
	 * @Return:
	 * @Descb: 获取借款投标的状态,条件是正在招标中
	 * @Throws:
	 */
	public Map<String, String> getInvestStatus(Connection conn, long id)
			throws SQLException, DataException {
		Dao.Tables.t_borrow t_borrow_invest = new Dao().new Tables().new t_borrow();
		DataSet dataSet = t_borrow_invest.open(conn, " id,hasPWD ,nid_log,hasCirculationNumber,circulationNumber,smallestFlowUnit ",
				" borrowStatus =2 and id=" + id, " id desc", -1, -1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	/**
	 * @MethodName: queryBorrowInvest
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-11 下午06:52:43
	 * @Return:
	 * @Descb: 根据ID获取借款投标中的借款内容
	 * @Throws:
	 */
	public Map<String, String> queryBorrowInvest(Connection conn, long id)
			throws SQLException, DataException {
		Dao.Views.v_t_borrow_invest v_t_borrow_invest = new Dao().new Views().new v_t_borrow_invest();
		DataSet dataSet = v_t_borrow_invest.open(conn, " * ", " id=" + id, "",
				0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	/**
	 * @MethodName: queryUserMonney
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-12 下午08:47:41
	 * @Return:
	 * @Descb: 查询用户的金额
	 * @Throws:
	 */
	public Map<String, String> queryUserMonney(Connection conn, long userId)
			throws SQLException, DataException {
		Dao.Tables.t_user t_user = new Dao().new Tables().new t_user();
		DataSet dataSet = t_user.open(conn,
				" (usableSum+freezeSum) AS totalSum,usableSum ", " id="
						+ userId, "", 0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**
	 * @MethodName: queryUserMonney
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-12 下午08:47:41
	 * @Return:
	 * @Descb: 查询投标是否满额
	 * @Throws:
	 */
	public Map<String, String> queryIsFull(Connection conn, long borrowId)
			throws SQLException, DataException {
		Dao.Tables.t_borrow borrow = new Dao().new Tables().new t_borrow();
		DataSet dataSet = borrow.open(conn,
				" borrowAmount,hasInvestAmount ", " id="
						+ borrowId, "", 0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**
	 * @MethodName: queryUserMonney
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-12 下午08:47:41
	 * @Return:
	 * @Descb: 
	 * @Throws:
	 */
	public long updateFullBorrowTime(Connection conn, long borrowId)
			throws SQLException, DataException {
		Dao.Tables.t_borrow borrow = new Dao().new Tables().new t_borrow();
		borrow.fullBorrowTime.setValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
		long result = borrow.update(conn, " id="+borrowId);
		return result;
	}

	/**
	 * @MethodName: addBrowseCount
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-5 下午03:51:36
	 * @Return:
	 * @Descb: 更新浏览量
	 * @Throws:
	 */
	public long addBrowseCount(Connection conn, Long id) throws SQLException {
		long returnId = -1L;
		SimpleDateFormat sf = new SimpleDateFormat(UtilDate.simple);
		StringBuffer condition = new StringBuffer();
		condition.append("UPDATE t_borrow  SET remainTimeStart = '"
				+ sf.format(new Date()));
		condition.append("' WHERE remainTimeEnd IS NOT NULL AND ");
		condition.append(" remainTimeStart <remainTimeEnd AND id =" + id);
		returnId = MySQL.executeNonQuery(conn,
				" update t_borrow set visitors = visitors+1 where id = " + id);
		MySQL.executeNonQuery(conn, condition.toString());
		condition=null;
		return returnId;
	}

	/**
	 * @MethodName: valideInvest
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-12 下午04:06:58
	 * @Return:
	 * @Descb: 验证投资人是否符合本次投标
	 * @Throws:
	 */
	public String valideInvest(Connection conn, long id, long userId,
			double amount) throws SQLException, DataException {
		int returnId = 0;
		Dao.Tables.t_user t_user = new Dao().new Tables().new t_user();
		Dao.Tables.t_borrow t_borrow = new Dao().new Tables().new t_borrow();

		DataSet investAmountDataSet = t_borrow.open(conn, " id ", " id=" + id
				+ " and (borrowAmount-hasInvestAmount) >=" + amount, "", 0, 1);
		returnId = investAmountDataSet.tables.get(0).rows.getCount();
		if (returnId == 0) {
			return "您的投标金额超过本轮剩余投标金额";
		} else {
			DataSet maxTenderedSumDataSet = t_borrow.open(conn,
					" maxTenderedSum ", " id=" + id, "", 0, 1);
			returnId = maxTenderedSumDataSet.tables.get(0).rows.getCount();
			DataRow dr = maxTenderedSumDataSet.tables.get(0).rows.get(0);
			BigDecimal maxTenderedSum = (BigDecimal) dr.get("maxTenderedSum");
			if (maxTenderedSum != null) {
				if (returnId == 0) {
					return "您的投标金额超过本轮最多投标金额";
				} else {
					DataSet usableSumDataSet = t_user.open(conn, " id ", " id="
							+ userId + " and usableSum >" + amount, "", 0, 1);
					returnId = usableSumDataSet.tables.get(0).rows.getCount();
					if (returnId == 0) {
						return "您的可用余额不够进行本轮投标";
					}
				}
			} else {
				DataSet usableSumDataSet = t_user.open(conn, " id ", " id="
						+ userId + " and usableSum >" + amount, "", 0, 1);
				returnId = usableSumDataSet.tables.get(0).rows.getCount();
				if (returnId == 0) {
					return "您的可用余额不够进行本轮投标";
				}
			}
		}
		return "";
	}

	/**
	 * @MethodName: valideTradePassWord
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-12 下午04:07:23
	 * @Return:
	 * @Descb: 验证交易密码
	 * @Throws:
	 */
	public String valideTradePassWord(Connection conn, long userId, String pwd)
			throws SQLException, DataException {
		if ("1".equals(IConstants.ENABLED_PASS)) {
			pwd = com.shove.security.Encrypt.MD5(pwd.trim());
		} else {
			pwd = com.shove.security.Encrypt.MD5(pwd.trim()
					+ IConstants.PASS_KEY);
		}
		Dao.Tables.t_user t_user = new Dao().new Tables().new t_user();

		DataSet dataSet = t_user.open(conn, " id ", " id=" + userId
				+ " and dealpwd ='" + StringEscapeUtils.escapeSql(pwd) + "'", "", 0, 1);
		Map<String, String> map = BeanMapUtils.dataSetToMap(dataSet);
		if (map == null || map.size() == 0) {
			return "交易密码错误";
		}
		return "";
	}

	/**
	 * @MethodName: addBorrowInvest
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-12 下午05:46:28
	 * @Return:
	 * @Descb: 添加借款投资
	 * @Throws:
	 */
	public Long addBorrowInvest(Connection conn, long id, long userId,
			double borrowSum, double annualRate,double deadlineDouble) throws SQLException {
		long returnId = -1;
		// 添加投资记录
		Dao.Tables.t_invest t_invest = new Dao().new Tables().new t_invest();
		t_invest.investAmount.setValue(borrowSum);
		t_invest.realAmount.setValue(borrowSum);
		t_invest.monthRate.setValue(annualRate/12);
		t_invest.investor.setValue(userId);
		t_invest.oriInvestor.setValue(userId);
		t_invest.investTime.setValue(new Date());
		t_invest.borrowId.setValue(id);
		t_invest.deadline.setValue(deadlineDouble);
		returnId = t_invest.insert(conn);
		// 更新借款信息中的已投资总额和数量
		returnId = MySQL.executeNonQuery(conn,
				" update t_borrow set hasInvestAmount = hasInvestAmount+"
						+ borrowSum + ",investNum=investNum+1" + " where id = "
						+ id);
		// 更新投资人的资金信息
		returnId = MySQL.executeNonQuery(conn,
				" update t_user set usableSum = usableSum-" + borrowSum
						+ ",freezeSum=freezeSum+" + borrowSum + " where id = "
						+ userId);
		return returnId;
	}

	/**
	 * @MethodName: isFullSale
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-17 上午12:44:07
	 * @Return:
	 * @Descb: 判断是否符合满标的条件投资金额已经达到借款金额
	 * @Throws:
	 */
	public Map<String, String> isFullSale(Connection conn, long id)
			throws SQLException, DataException {
		StringBuffer condition = new StringBuffer();
		condition.append(" borrowStatus =" + IConstants.BORROW_STATUS_2);
		condition.append(" and borrowAmount = hasInvestAmount");
		condition.append(" and remainTimeStart < remainTimeEnd");
		condition.append(" and id=" + id);
		Dao.Tables.t_borrow t_borrow_invest = new Dao().new Tables().new t_borrow();
		DataSet dataSet = t_borrow_invest.open(conn, " id ", condition
				.toString(), " id desc", -1, -1);
		condition = null;
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	/**
	 * @MethodName: updateBorrowFullSale
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-17 上午12:57:53
	 * @Return:
	 * @Descb: 更新借款的状态为满标
	 * @Throws:
	 */
	public long updateBorrowFullSale(Connection conn, long id,int sorts)
			throws SQLException, DataException {
		long returnId = -1L;
		StringBuffer condition = new StringBuffer();
		condition.append("update t_borrow set ");
		condition.append(" sort = "+sorts);
		condition.append(", borrowStatus =" + IConstants.BORROW_STATUS_3);
		condition.append(",remainTimeStart= remainTimeEnd");
		condition.append(" where id =" + id);
		returnId = MySQL.executeNonQuery(conn, condition.toString());
		condition = null;
		return returnId;
	}

	/**
	 * @MethodName: addBorrowMSG
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-13 上午08:15:42
	 * @Return:
	 * @Descb: 添加借款留言
	 * @Throws:
	 */
	public Long addBorrowMSG(Connection conn, long id, long userId,
			String msgContent) throws SQLException {
		Dao.Tables.t_msgboard t_msgboard = new Dao().new Tables().new t_msgboard();
		t_msgboard.msgContent.setValue(msgContent);
		t_msgboard.modeId.setValue(id);
		// 借款留言类型
		t_msgboard.msgboardType.setValue(1);
		t_msgboard.msger.setValue(userId);
		t_msgboard.msgTime.setValue(new Date());
		return t_msgboard.insert(conn);
	}

	/**
	 * @MethodName: addFocusOn
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-16 上午08:58:05
	 * @Return:
	 * @Descb: 我的关注
	 * @Throws:
	 */
	public Long addFocusOn(Connection conn, long id, long userId, int moduleType)
			throws SQLException {
		Dao.Tables.t_concern t_concern = new Dao().new Tables().new t_concern();
		t_concern.moduleId.setValue(id);
		t_concern.userId.setValue(userId);
		t_concern.moduleType.setValue(moduleType);
		return t_concern.insert(conn);
	}

	/**
	 * @MethodName: hasFocusOn
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-16 上午11:02:03
	 * @Return:
	 * @Descb: 查询用户是否已经有关注
	 * @Throws:
	 */
	public Map<String, String> hasFocusOn(Connection conn, long id,
			long userId, int moduleType) throws SQLException, DataException {
		String condition = "moduleId = " + id + " and userId = " + userId
				+ " and moduleType=" + moduleType;
		Dao.Tables.t_concern t_concern = new Dao().new Tables().new t_concern();
		DataSet dataSet = t_concern.open(conn, " id ", condition, "", 0, 1);
		condition =  null;
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	/**
	 * @MethodName: addUserMail
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-16 下午09:59:59
	 * @Return:
	 * @Descb: 添加用户站内信
	 * @Throws:
	 */
	public Long addUserMail(Connection conn, long reciver, Long userId,
			String title, String content, int mailType) throws SQLException {
		Dao.Tables.t_mail t_mail = new Dao().new Tables().new t_mail();
		t_mail.reciver.setValue(reciver);
		t_mail.sender.setValue(userId);
		t_mail.mailTitle.setValue(title);
		t_mail.mailContent.setValue(content);
		t_mail.mailType.setValue(mailType);
		t_mail.mailMode.setValue(1);
		t_mail.sendTime.setValue(new Date());
		return t_mail.insert(conn);
	}

	/**
	 * @MethodName: addUserReport
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-16 下午10:13:31
	 * @Return:
	 * @Descb:
	 * @Throws:
	 */
	public Long addUserReport(Connection conn, long reporter, Long userId,
			String title, String content) throws SQLException {
		Dao.Tables.t_report t_report = new Dao().new Tables().new t_report();
		t_report.reporter.setValue(reporter);
		t_report.user.setValue(userId);
		t_report.reportTitle.setValue(title);
		t_report.reportContent.setValue(content);
		t_report.reportTime.setValue(new Date());
		return t_report.insert(conn);
	}
	
	public List<Map<String, Object>> queryJrlLastestBorrow(Connection conn)
			throws SQLException, DataException {
		Dao.Views.v_t_borrow_list v_t_borrow_list = new Dao().new Views().new v_t_borrow_list();
		DataSet dataSet = v_t_borrow_list
				.open(
						conn,
						" * ",
						" sorts <> 0 and parentBId < 0 and agent = 75  and borrowStatus > 1 ",
						" borrowStatus asc , schedules desc  ", 0, 2);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	
	/**
	 * @MethodName: queryJxbLastestBorrow
	 * @Param: FinanceDao
	 * @Author: Mr.Zero
	 * @Date: 2017-4-18 下午午16:00:00
	 * @Return:
	 * @Descb:  加薪宝
	 * @Throws:
	 */
	public List<Map<String, Object>> queryJxbLastestBorrow(Connection conn)
			throws SQLException, DataException {
		Dao.Views.v_t_borrow_list v_t_borrow_list = new Dao().new Views().new v_t_borrow_list();
		DataSet dataSet = v_t_borrow_list
				.open(
						conn,
						" * ",
						" sorts <> 0 and parentBId < 0 and agent = 79  and borrowStatus > 1 ",
						" borrowStatus asc , schedules desc  ", 0, 1);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}

	/**
	 * @throws DataException
	 * @throws SQLException
	 * @MethodName: queryLastBorrow
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-18 上午09:25:24
	 * @Return:
	 * @Descb:
	 * @Throws:
	 */
	public List<Map<String, Object>> queryLastestBorrow(Connection conn)
			throws SQLException, DataException {
		Dao.Views.v_t_borrow_list v_t_borrow_list = new Dao().new Views().new v_t_borrow_list();
		DataSet dataSet = v_t_borrow_list.open(conn, " * ", " sorts != 0  and borrowStatus > 1 and parentBId < 0 and isForNewUser = 0  and agent = 76 ", " borrowStatus asc , schedules desc  ", 0,
				5);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	//最新的稳赢宝
	public Map<String, String> queryWyb(Connection conn) throws SQLException, DataException {
		Dao.Views.v_t_borrow_list v_t_borrow_list = new Dao().new Views().new v_t_borrow_list();
		DataSet dataSet = v_t_borrow_list.open(conn, " * ", " sorts != 0  and borrowStatus > 1 and parentBId < 0 and isForNewUser = 0 ", " borrowStatus asc , schedules desc  ", 0,
				1);
		dataSet.tables.get(0).rows.genRowsMap();
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public List<Map<String, Object>> queryLastestNewUserBorrow(Connection conn)
	throws SQLException, DataException {
		Dao.Views.v_t_borrow_list v_t_borrow_list = new Dao().new Views().new v_t_borrow_list();
		DataSet dataSet = v_t_borrow_list.open(conn, " * ", " sorts != 0  and borrowStatus > 1 and parentBId < 0 and isForNewUser = 1 ", " borrowStatus asc , schedules desc  ", 0,
		5);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	public Map<String, String> queryNewUserBorrow(Connection conn)
			throws SQLException, DataException {
		Dao.Views.v_t_borrow_list v_t_borrow_list = new Dao().new Views().new v_t_borrow_list();
		DataSet dataSet = v_t_borrow_list.open(conn, " * ", " sorts != 0  and borrowStatus > 1 and parentBId < 0 and isForNewUser = 1 ", " borrowStatus asc , schedules desc  ", 0,
		1);
		dataSet.tables.get(0).rows.genRowsMap();
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public Map<String, String> querytBorrowProduct(Connection conn , int productType)
			throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append(" productStatus > 0 and type = " + productType);
		if(productType == 1){
			sql.append(" and pid = 342 ");
		}
		Dao.Views.v_t_product v_t_product = new Dao().new Views().new v_t_product();
		DataSet dataSet = v_t_product.open(conn, " * ",
				sql.toString(),
				" productStatus asc,publishTime DESC ",0,1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	//根据余额宝的pid查询余额宝投资总记录放在首页显示
	public Map<String, String> querytYebInvestTotal(Connection conn , String pid)
			throws SQLException, DataException {
		Dao.Views.v_t_yeb_invest_detail v_t_yeb_invest_detail = new Dao().new Views().new v_t_yeb_invest_detail();
		DataSet dataSet = v_t_yeb_invest_detail.open(conn, " count(*) as totalSum ",
				" productId = " + pid,
				"",0,1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	//统计所有余额宝的累计加入次数
	public Map<String, String> querytYebInvestTotalAll(Connection conn)
			throws SQLException, DataException {
		Dao.Views.v_t_yeb_invest_detail v_t_yeb_invest_detail = new Dao().new Views().new v_t_yeb_invest_detail();
		DataSet dataSet = v_t_yeb_invest_detail.open(conn, " count(id) as totalSum ","","",0,1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public List<Map<String, Object>> queryLastestDebt(Connection conn)
			throws SQLException, DataException {
		Dao.Views.v_t_assignment_debt v_t_assignment_debt = new Dao().new Views().new v_t_assignment_debt();
		DataSet dataSet = v_t_assignment_debt.open(conn, " * ", " debtStatus = 2 ", " debtStatus asc,applyTime desc  ", 0,5);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	
	public Map<String, String> queryDebt(Connection conn)
			throws SQLException, DataException {
		Dao.Views.v_t_assignment_debt v_t_assignment_debt = new Dao().new Views().new v_t_assignment_debt();
		DataSet dataSet = v_t_assignment_debt.open(conn, " * ", " debtStatus >=2 and debtStatus <=3 ", " debtStatus asc,applyTime desc  ", 0,1);
		dataSet.tables.get(0).rows.genRowsMap();
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**   
	 * @MethodName: investRank  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-3-18 上午11:10:04
	 * @Return:    
	 * @Descb: 投资排名前8条记录
	 * @Throws:
	*/
	public List<Map<String, Object>> investRank(Connection conn,String starTime,String endTime)
			throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT SUM(t.borrowSum) AS borrowSum,f_formatting_username(b.username) as username,f_rating(b.rating) AS rating FROM(");
		sql.append(" SELECT SUM(realAmount) AS borrowSum,investor,investTime FROM t_invest ");
		if (StringUtils.isNotBlank(starTime)) {
			sql.append(" where investTime >= '"+StringEscapeUtils.escapeSql(starTime)+"'") ;
		}
		if (StringUtils.isNotBlank(endTime)) {
			sql.append("  and investTime <='"+StringEscapeUtils.escapeSql(endTime)+"'") ;
		}
		sql.append(" GROUP BY investor");
		sql.append(" UNION ALL SELECT SUM(realAmount) AS borrowSum,investor,investTime FROM t_invest_history ");
		if (StringUtils.isNotBlank(starTime)) {
			sql.append(" where investTime >= '"+StringEscapeUtils.escapeSql(starTime)+"'") ;
		}
		if (StringUtils.isNotBlank(endTime)) {
			sql.append("  and investTime <='"+StringEscapeUtils.escapeSql(endTime)+"'") ;
		}
		sql.append("GROUP BY investor");
		sql.append(" )t LEFT JOIN t_user b ON t.investor = b.id GROUP BY t.investor ORDER BY borrowSum DESC LIMIT 0,8");
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		dataSet.tables.get(0).rows.genRowsMap();
		sql = null;
		return dataSet.tables.get(0).rows.rowsMap;
	}
	
	
	/**   
	 * @MethodName: queryTotalRisk  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-3-18 下午01:33:42
	 * @Return:    
	 * @Descb: 查询风险保障金总额
	 * @Throws:
	*/
	public Map<String,String> queryTotalRisk(Connection conn) throws SQLException, DataException{
		DataSet dataSet = MySQL.executeQuery(conn, "SELECT ((SUM(riskInCome)-SUM(riskSpending))) AS total FROM t_risk_detail limit 0,1");
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**   
	 * @MethodName: queryCurrentRisk  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-3-18 下午01:33:46
	 * @Return:    
	 * @Descb: 查询当日风险保障金收支金额
	 * @Throws:
	*/
	public Map<String,String> queryCurrentRisk(Connection conn) throws SQLException, DataException{
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sf.format(new Date());
		Dao.Tables.t_risk_detail t_risk_detail = new Dao().new Tables().new t_risk_detail();
		DataSet dataSet = t_risk_detail.open(conn, " sum(riskInCome) as riskInCome,sum(riskSpending) as riskSpending ", " riskDate='" +date+"'",
				"", 0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	
	/**   
	 * @MethodName: getInvestPWD  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-5 下午05:38:27
	 * @Return:    
	 * @Descb: 获取投标密码是否正确
	 * @Throws:
	*/
	public Map<String, String> getInvestPWD(Connection conn,long idLong, String investPWD) throws SQLException, DataException {
		Dao.Tables.t_borrow t_borrow = new Dao().new Tables().new t_borrow();
		DataSet dataSet = t_borrow.open(conn, " id ", " id="+idLong+" and investPWD='"+StringEscapeUtils.escapeSql(investPWD.trim())+"'",
				"", 0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**
	 * 查找投资人信息
	 * add by houli
	 * @return
	 * @throws DataException 
	 * @throws SQLException 
	 */
	public Map<String,String> queryInvestorById(Connection conn,long investorId,int limitStart,int limitCount) throws SQLException, DataException{
		Dao.Tables.t_invest t_invest = new Dao().new Tables().new t_invest();
		DataSet dataSet = t_invest.open(conn, " investor ", 
				" investor="+investorId,"",0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	
	/**   
	 * @MethodName: queryInvestIdByFlag  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-11 下午05:38:44
	 * @Return:    
	 * @Descb: 查询投资的id
	 * @Throws:
	*/
	public Map<String, String> queryInvestIdByFlag(Connection conn, String flag) throws SQLException, DataException {
		Dao.Tables.t_invest t_invest = new Dao().new Tables().new t_invest();
		DataSet dataSet = t_invest.open(conn, " id ", 
				" flag="+flag,"",0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	
	/**   
	 * @throws DataException 
	 * @throws SQLException 
	 * @MethodName: queryUserImageByid  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-16 上午11:21:25
	 * @Return:    
	 * @Descb: 查询用户认证通过的图片
	 * @Throws:
	*/
	public List<Map<String, Object>> queryUserImageByid(Connection conn,
			long typeId, long userId) throws SQLException, DataException {
		Dao.Views.v_t_borrow_user_materauth_img user_materialsauth_img = new Dao().new Views().new v_t_borrow_user_materauth_img();
		DataSet dataSet = user_materialsauth_img.open(conn, " * ", " materAuthTypeId=" + typeId+" and userId="+userId, "",
				-1, -1);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}

	/**   
	 * @MethodName: queBorrowInfo  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-21 下午01:56:10
	 * @Return:    
	 * @Descb: 查询借款信息
	 * @Throws:
	*/
	public Map<String, String> queBorrowInfo(Connection conn, long id) throws DataException, SQLException {
		StringBuffer command = new StringBuffer();
		command.append(" select a.borrowWay as borrowWay,a.auditTime as auditTime, a.excitationType as excitationType,a.excitationSum as excitationSum,a.circulationNumber as circulationNumber, a.version,a.annualRate,(a.annualRate/12) monthRate");
		command.append(",a.borrowAmount,a.deadline,a.borrowTitle,a.publisher,a.isDayThe,b.username as borrowerName from");
		command.append(" t_borrow a left join t_user b on a.publisher=b.id where a.id="+ id);
		DataSet ds = MySQL.executeQuery(conn, command.toString());
		return BeanMapUtils.dataSetToMap(ds);
	}
	

	/**   
	 * @MethodName: addInvest  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-21 下午02:04:59
	 * @Return:    
	 * @Descb: 添加投资记录
	 * @Throws:
	*/
	public long addInvest(Connection conn, double investAmount,
			double realAmount, double monthRate, long investor,long oriInvestor, long id, int deadline,int isAutoBid) throws SQLException {
		Dao.Tables.t_invest t_invest = new Dao().new Tables().new t_invest();
		t_invest.investAmount.setValue(investAmount);
		t_invest.realAmount.setValue(realAmount);
		t_invest.monthRate.setValue(monthRate);
		t_invest.investor.setValue(investor);
		t_invest.oriInvestor.setValue(oriInvestor);
		t_invest.borrowId.setValue(id);
		t_invest.deadline.setValue(deadline);
		t_invest.investTime.setValue(new Date());
		t_invest.isAutoBid.setValue(isAutoBid);
		return t_invest.insert(conn);
	}
	
	/**
	 * @MethodName: updateBorrowStatus
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-4-21 下午04:47:06
	 * @Return:
	 * @Descb: 更新借款状态
	 * @Throws:
	 */
	public long updateBorrowStatus(Connection conn, double investAmount,
			 long copies,long id,int version) throws SQLException {
		long returnId = -1;
		StringBuffer command = new StringBuffer();
		command.append("UPDATE t_borrow SET hasInvestAmount = hasInvestAmount+ "+investAmount);
		command.append(",hasCirculationNumber=hasCirculationNumber+"+copies);
		command.append(",investNum=investNum+1,version=version+1 WHERE id =" + id);
		command.append(" and hasInvestAmount <borrowAmount and version="+version);
		returnId = MySQL.executeNonQuery(conn, command.toString());
		return returnId;
	}
	
	

	/**   
	 * @MethodName: freezeUserAmount  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-21 下午04:48:11
	 * @Return:    
	 * @Descb: 投资人投资成功资金冻结
	 * @Throws:
	*/
	public long freezeUserAmount(Connection conn, double investAmount,
			long userId) throws SQLException {
		long returnId = -1;
		StringBuffer command = new StringBuffer();
		command.append("UPDATE t_user SET usableSum = usableSum-"+investAmount+", freezeSum=freezeSum+"+investAmount+" WHERE id ="+userId);
		returnId = MySQL.executeNonQuery(conn, command.toString());
		command = null;
		return returnId;
	}
	
	
	/**   
	 * @MethodName: updateFullBorrow  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-21 下午04:51:11
	 * @Return:    
	 * @Descb: 更新满标的借款
	 * @Throws:
	*/
	public long updateFullBorrow(Connection conn,long id) throws SQLException{
		long returnId = -1;
		StringBuffer command = new StringBuffer();
		command.append("update t_borrow set borrowStatus =3,sort = 5,remainTimeStart= remainTimeEnd,fullBorrowTime=now()  where borrowAmount=hasInvestAmount AND borrowStatus = 2 AND id = "+id);
		returnId = MySQL.executeNonQuery(conn, command.toString());
		command = null; 
		return returnId;
	}

	
	/**   
	 * @MethodName: queryUserAmountAfterHander  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-21 下午05:06:05
	 * @Return:    
	 * @Descb: 查询用户操作后的资金记录
	 * @Throws:
	*/
	public Map<String, String> queryUserAmountAfterHander(Connection conn,
			long userId) throws SQLException, DataException {
		StringBuffer command = new StringBuffer();
		command.append("select ifnull(a.usableSum,0) usableSum,ifnull(a.freezeSum,0) freezeSum,ifnull(sum(b.recivedPrincipal+b.recievedInterest-b.hasPrincipal-b.hasInterest),0.0) forPI ,a.lastIP as lastIP ");
		command.append(" from t_user a left join t_invest b on a.id = b.investor where a.id="+userId+" group by a.id");
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		command = null;
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	
	/**   
	 * @MethodName: addUserDynamic  
	 * @Param: BorrowDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-21 上午10:28:50
	 * @Return:    
	 * @Descb: 添加用户动态
	 * @Throws:
	*/
	public long addUserDynamic(Connection conn, long userId, String url) throws SQLException {
		Dao.Tables.t_user_recorelist t_user_recorelist = new Dao().new Tables().new t_user_recorelist();
		t_user_recorelist.userId.setValue(userId);
		t_user_recorelist.url.setValue(url);
		t_user_recorelist.time.setValue(new Date());
		return t_user_recorelist.insert(conn);
	}
	

	/**   
	 * @MethodName: updateFullScall  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-25 上午10:01:50
	 * @Return:    
	 * @Descb: 更新满标的状态
	 * @Throws:
	*/
	public void updateFullScall(Connection conn, long id) throws SQLException {
        StringBuffer command = new StringBuffer();
        command.append("update t_borrow set borrowStatus =3,remainTimeStart= remainTimeEnd where borrowAmount=hasInvestAmount AND borrowStatus = 2 and id ="+id);
        MySQL.executeNonQuery(conn, command.toString());
        command = null;
	}

	
	/**   
	 * @throws DataException 
	 * @throws SQLException 
	 * @MethodName: queryBorrowTenderIn  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-25 上午10:03:40
	 * @Return:    
	 * @Descb: 查询招标中的借款
	 * @Throws:
	*/
	public Map<String, String> queryBorrowTenderIn(Connection conn, long id) throws DataException, SQLException {
		StringBuffer command = new StringBuffer();
		command.append("SELECT id,version,smallestFlowUnit,(circulationNumber-hasCirculationNumber) as remainCirculationNumber FROM t_borrow WHERE "
				+ "borrowAmount>hasInvestAmount AND borrowStatus = 2 AND id ="
				+ id);
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		command = null;
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**   
	 * @throws DataException 
	 * @throws SQLException 
	 * @MethodName: queryBorrowTenderIn  
	 * @Param: FinanceDao  
	 * @Author:
	 * @Date: 
	 * @Return:    
	 * @Descb: 查询所有招标中的借款
	 * @Throws:
	*/
	public List<Map<String, Object>>  queryAllBorrowTenderIn(Connection conn) throws DataException, SQLException {
		Dao.Views.v_t_borrow_list v_t_borrow_list = new Dao().new Views().new v_t_borrow_list();
		DataSet dataSet = v_t_borrow_list.open(conn, " * ", " sorts != 0  and borrowStatus = 2", " borrowStatus asc , schedules desc  ", 0,
				10);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	
	/**   
	 * @throws DataException 
	 * @throws SQLException 
	 * @MethodName: queryInvestAmount  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-25 上午10:06:31
	 * @Return:    
	 * @Descb: 本轮剩余投标金额
	 * @Throws:
	*/
	public Map<String, String> queryInvestAmount(Connection conn, long id,double investAmount) throws SQLException, DataException {
		StringBuffer command = new StringBuffer();
		command.append("SELECT id FROM t_borrow WHERE (borrowAmount-hasInvestAmount) >="+investAmount+"  AND id ="+id);
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		command = null;
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	
	/**   
	 * @throws DataException 
	 * @throws SQLException 
	 * @MethodName: queryMinTenderedSum  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-25 上午10:19:15
	 * @Return:    
	 * @Descb: 查询最小投标金额
	 * @Throws:
	*/
	public Map<String, String> queryMinTenderedSum(Connection conn, long id,
			double investAmount) throws SQLException, DataException {  
	    StringBuffer command = new StringBuffer();
		command.append("SELECT id FROM t_borrow WHERE minTenderedSum > (borrowAmount-hasInvestAmount) and id ="+id);
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		command = null;
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	
	/**   
	 * @throws DataException 
	 * @throws SQLException 
	 * @MethodName: queryUserUsableSum  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-25 上午10:19:25
	 * @Return:    
	 * @Descb: 查询用户可用金额
	 * @Throws:
	*/
	public Map<String, String> queryUserUsableSum(Connection conn, long userId,double investAmount) throws SQLException, DataException {
		StringBuffer command = new StringBuffer();
		command.append("SELECT id,usableSum  FROM t_user WHERE usableSum < "+investAmount+" and id ="+userId);
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		command = null;
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	
	/**   
	 * @throws DataException 
	 * @throws SQLException 
	 * @MethodName: queryMaxTenderedSum  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-25 上午10:24:07
	 * @Return:    
	 * @Descb: 查询最大投标金额
	 * @Throws:
	*/
	public Map<String, String> queryMaxTenderedSum(Connection conn, long id,
			double investAmount) throws DataException, SQLException {
		StringBuffer command = new StringBuffer();
		command.append("SELECT id FROM t_borrow WHERE maxTenderedSum < "+investAmount+" and maxTenderedSum is not null and id ="+id);
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		command = null;
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	
	/**   
	 * @throws DataException 
	 * @throws SQLException 
	 * @MethodName: queryMinTenderedSumMaps  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-25 上午10:26:49
	 * @Return:    
	 * @Descb: 查询本轮最小投标金额
	 * @Throws:
	*/
	public Map<String, String> queryMinTenderedSumMaps(Connection conn,
			long id, double investAmount) throws SQLException, DataException {
		StringBuffer command = new StringBuffer();
		command.append("SELECT id FROM t_borrow WHERE minTenderedSum > "+investAmount+" and minTenderedSum is not null and id ="+id);
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		command = null;
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	
	/**   
	 * @MethodName: addAutoBidRecord  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-4-27 上午11:47:20
	 * @Return:    
	 * @Descb: 添加自动投标用户投标记录
	 * @Throws:
	*/
	public long addAutoBidRecord(Connection conn, long userId, int borrowId) throws SQLException {
		Dao.Tables.t_automaticbid_user t_automaticbid_user = new Dao().new Tables().new t_automaticbid_user();
		t_automaticbid_user.userId.setValue(userId);
		t_automaticbid_user.borrowId.setValue(borrowId);
		return t_automaticbid_user.insert(conn);
	}

	/**   
	 * @MethodName: queryHasInvest  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-5-14 上午10:54:46
	 * @Return:    
	 * @Descb: 查询已投资总额是否小于等于借款总额
	 * @Throws:
	*/
	public Map<String, String> queryHasInvest(Connection conn, long id) throws SQLException, DataException {
		StringBuffer command = new StringBuffer();
		command.append("select id from t_borrow where hasInvestAmount <=borrowAmount and id ="+id);
		DataSet dataSet = Database.executeQuery(conn, command.toString());
		command = null;
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	/**   
	 * @MethodName: reBackBorrowStatus  
	 * @Param: FinanceDao  
	 * @Author: gang.lv
	 * @Date: 2013-5-14 下午04:56:11
	 * @Return:    
	 * @Descb: 回退借款信息中的已投资总额和数量
	 * @Throws:
	*/
	public long reBackBorrowStatus(Connection conn, double bidAmount,
			int borrowId) throws SQLException {
		StringBuffer command = new StringBuffer();
		command.append("UPDATE t_borrow SET hasInvestAmount = hasInvestAmount- "+bidAmount+",investNum=investNum-1 WHERE id ="+borrowId);
		long result= Database.executeNonQuery(conn, command.toString());
		command = null;
		return result;
	}
	
	/**
	 * @MethodName: updateRepo
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-5-21 上午09:05:09
	 * @Return:
	 * @Descb: 更新回购中的状态
	 * @Throws:
	 */
	public void updateRepo(Connection conn, Long id) throws SQLException {
		String command = "update t_borrow set borrowStatus =4 where borrowAmount=hasInvestAmount AND borrowStatus = 2 and id ="
						+ id;
		MySQL.executeNonQuery(conn, command.toString());
		command = null;
	}

	public long addCirculatioinInvest(Connection conn, double investAmount,
			double realAmount, double monthRate, long investor,
			long oriInvestor, long id, int deadline,
			int circulationForpayStatus, double circulationInterest,Date repayDate,
			double excutation,String reason,int isAutoBid)
			throws SQLException {
		Dao.Tables.t_invest t_invest = new Dao().new Tables().new t_invest();
		t_invest.investAmount.setValue(investAmount);
		t_invest.realAmount.setValue(realAmount);
		t_invest.monthRate.setValue(monthRate);
		t_invest.investor.setValue(investor);
		t_invest.oriInvestor.setValue(oriInvestor);
		t_invest.borrowId.setValue(id);
		t_invest.deadline.setValue(deadline);
		t_invest.investTime.setValue(new Date());
		if(repayDate !=null){
			t_invest.repayDate.setValue(repayDate);			
		}
		t_invest.circulationForpayStatus.setValue(circulationForpayStatus);
		t_invest.circulationInterest.setValue(circulationInterest);
		t_invest.recivedPrincipal.setValue(investAmount);
		t_invest.recievedInterest.setValue(circulationInterest);
		t_invest.reward.setValue(excutation);
		t_invest.reason.setValue(reason);
		t_invest.isAutoBid.setValue(isAutoBid);
		
		return t_invest.insert(conn);
	}
	/**
	 * @throws SQLException
	 * @MethodName: addUserAmount
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-5-21 上午11:04:23
	 * @Return:
	 * @Descb: 添加用户金额
	 * @Throws:
	 */
	public long addUserAmount(Connection conn, double investAmount, long userId)
			throws SQLException {
		StringBuffer command = new StringBuffer();
		command.append("update t_user set usableSum =usableSum+" + investAmount
				+ " where id =" + userId);
		return MySQL.executeNonQuery(conn, command.toString());
	}
	/**
	 * @MethodName: deductedUserAmount
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-5-21 上午11:04:14
	 * @Return:
	 * @Descb: 扣除用户金额
	 * @Throws:
	 */
	public long deductedUserAmount(Connection conn, double investAmount,
			long userId) throws SQLException {
		StringBuffer command = new StringBuffer();
		command.append("update t_user set usableSum =usableSum-" + investAmount
				+ " where id =" + userId);
		return MySQL.executeNonQuery(conn, command.toString());
	}
	
	/**
	 * 获取离现在时间最近的一条定时发布的标的  add by tanghaifeng
	 * @param conn
	 * @return
	 * @throws Exception
	 */
	public Map<String,String> queryBorrowByIstime(Connection conn)throws Exception{
		DataSet dataSet = MySQL.executeQuery(conn, "SELECT id,borrowTitle,min(publishTime) as publishTime FROM t_borrow where isTimes = 0 and publishTime > SYSDATE() and borrowStatus = 1");
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	/**
	 * 根据发布借款信息的用户,查询该用户正在还款中的借款  add by tanghaifeng
	 * @param conn
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> queryBorrowByUserId(Connection conn, String UserId)throws Exception{
		DataSet dataSet = 
			MySQL.executeQuery(conn, "SELECT publishTime,moneyPurposes,borrowAmount,annualRate,deadline,isDayThe FROM t_borrow WHERE " +
					"borrowStatus = 4 AND publisher = '" + UserId+"'");
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	/**
	 * 倒计时结束后更改该标的的状态
	 * @param conn
	 * @param timeBorrowId
	 * @return
	 * @throws SQLException
	 */
	public long changeTimeBorrowStaus(Connection conn,long timeBorrowId) throws SQLException {
		StringBuffer command = new StringBuffer();
		command.append("update t_borrow set borrowStatus = 2 "
				+ " where id =" + timeBorrowId +" and isTimes = 0");
		return MySQL.executeNonQuery(conn, command.toString());
	}
	/**
	 *  获取该标的相对应的担保公司  add by tanghaifeng
	 * @param conn
	 * @return
	 * @throws Exception
	 */
	public Map<String,String> getBorrowAgentByAgentId(Connection conn,long agentId)throws Exception{
		DataSet dataSet = MySQL.executeQuery(conn, "SELECT * FROM t_select where id = " + agentId );
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	//根据借款编号获取披露信息
	public List<Map<String, Object>>  getInfodisclosureList(Connection  conn, Long bid) throws SQLException, DataException{
		String sql = "SELECT * FROM t_infodisclosure WHERE bid = " + bid
				+" ORDER BY orderShort ASC";
		DataSet dataSet = MySQL.executeQuery(conn, sql);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	//根据产品编号获取披露信息
	public List<Map<String, Object>>  getInfodisclosureListByPid(Connection  conn, Long pid) throws SQLException, DataException{
		String sql = "SELECT a.* FROM t_infodisclosure a LEFT JOIN t_product_borrow b ON a.bid = b.bid WHERE b.pid = " + pid
				+" ORDER BY a.bid ASC,a.orderShort ASC";
		DataSet dataSet = MySQL.executeQuery(conn, sql);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	/**
	 *  获取该投资者投资收益相关信息  add by tanghaifeng
	 * @param conn
	 * @return
	 * @throws Exception
	 */
	public Map<String,String> getInvestorReceipts(Connection conn,long investor)throws Exception{
		DataSet dataSet = MySQL.executeQuery(conn, "" +
				"select count(*) number,cast(sum(tri.recivedPrincipal + tri.recivedInterest) as   decimal(10,   2)) total, cast(sum(tri.recivedPrincipal) as   decimal(10,   2)) totalInvest  from t_invest_repayment tri JOIN t_invest ti on ti.id = tri.invest_id where ti.investor = " + investor );
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	/***
	 * 根据用户Id获取用户所在公司信息
	 * @param conn
	 * @param id
	 * @return
	 * @throws DataException
	 */
	public Map<String, String> queryUserConpanyById(Connection conn, Long id) throws DataException {
		Dao.Views.v_t_user_conpany userConpany = new Dao().new Views().new v_t_user_conpany();
		DataSet dataSet = userConpany.open(conn, " * ", " id = " + id, " id desc", 0, 1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**
	 * 通过还款id获得所有所需收款的信息
	 */
	public List<Map<String, Object>> queryAllInvestInfo(Connection conn, long payId) throws SQLException, DataException {
		Dao.Views.v_t_chianpnr_repay user_materialsauth_img = new Dao().new Views().new v_t_chianpnr_repay();
		DataSet dataSet = user_materialsauth_img.open(conn, " * ", " payId=" + payId, "", -1, -1);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	//add by sunbing start
	/**
	 * 投资排名前50条记录
	 */
	public List<Map<String, Object>>  get_topNInvestment(Connection  conn, int count) throws SQLException, DataException{
		String sql = "SELECT v.rankingnow , v.investor , v.investCount, round(v.avgRate,2) AS weightedRateOfReturn, "
			+ " f_formatting_username(u.username) AS username , "
			+ " round(v.sumMoney, 2) AS sumMoney , CASE WHEN past.rownum IS NULL THEN v.rankingnow ELSE past.rownum END AS rankingpast "
			+ " FROM (SELECT investor , sumMoney , investCount , avgRate, (@rowNum := @rowNum + 1) AS rankingnow "
			+ " FROM (SELECT investor , sum(realAmount)  AS sumMoney ,count(investor) AS investCount ,avg(annualRate) AS avgRate "
			+ " FROM v_invest WHERE borrowStatus IN (4, 5) GROUP BY investor) a ,(SELECT @rowNum := 0) AS rowNum"
			+ " ORDER BY a.sumMoney DESC,a.investor LIMIT "+count+") v "
			+ " LEFT JOIN t_user u ON v.investor = u.id "
			+ " LEFT JOIN (SELECT (@rowNumP := @rowNumP + 1) AS rownum "
			+ " , v.investor AS investor , f_formatting_username(u.username) AS username "
			+ " , round(v.sumMoney, 2) AS sumMoney "
			+ " FROM (SELECT investor , sumMoney FROM (SELECT investor , sum(realAmount) AS sumMoney "
			+ " FROM v_invest WHERE borrowStatus IN (4, 5) "
			+ " AND date_format(investTime, '%y-%m-%d') < date_format(now(), '%y-%m-%d') "
			+ " GROUP BY investor) a ORDER BY a.sumMoney DESC,a.investor) v "
			+ " LEFT JOIN t_user u "
			+ " ON v.investor = u.id, (SELECT @rowNumP := 0) AS rowNumP) past "
			+ " ON v.investor = past.investor"
			;
		DataSet dataSet = MySQL.executeQuery(conn, sql);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	/**
	 * 本月新人王TOPN
	 */
	public List<Map<String, Object>>  get_mothTopNInvestment(Connection  conn, int count) throws SQLException, DataException{
		/*String sql = "SELECT (@rowNum := @rowNum + 1) AS rownum, "
			+ " v.investor,	f_formatting_username (v.username) AS username, "
			+ " round(v.sumMoney, 2) AS sumMoney, v.createTime "
			+ " FROM (SELECT investor,sumMoney,username,createTime "
			+ " FROM (SELECT vi.investor AS investor,sum(vi.realAmount) AS sumMoney,u.username AS username,u.createTime AS createTime "
			+ " FROM v_invest vi LEFT JOIN t_user u "
			+ " ON vi.investor = u.id "
			+ " WHERE borrowStatus IN (4, 5) "
			+ " AND YEAR (vi.investTime) = YEAR (now()) "
			+ " AND MONTH (vi.investTime) = MONTH (now()) "
			+ " AND MONTH (u.createTime) = MONTH (now()) "
			+ " GROUP BY investor) a "
			+ " ORDER BY a.sumMoney DESC,a.investor	LIMIT "+count+" ) v, "
			+ " (SELECT @rowNum := 0) AS rowNum "
			;*/
		//本月新人Top10 暂时修改为取距离当天1个月内的注册用户的数据
		String sql = "SELECT (@rowNum := @rowNum + 1) AS rownum, "
			+ " v.investor,	f_formatting_username (v.username) AS username, "
			+ " round(v.sumMoney, 2) AS sumMoney, v.createTime "
			+ " FROM (SELECT investor,sumMoney,username,createTime "
			+ " FROM (SELECT vi.investor AS investor,sum(vi.realAmount) AS sumMoney,u.username AS username,u.createTime AS createTime "
			+ " FROM v_invest vi LEFT JOIN t_user u "
			+ " ON vi.investor = u.id "
			+ " WHERE borrowStatus IN (4, 5) "
			+ " AND vi.investTime > date_sub(current_date (), INTERVAL 1 MONTH) "
			//+ " AND u.createTime > date_sub(current_date (), INTERVAL 1 MONTH) "
			+ " GROUP BY investor) a "
			+ " ORDER BY a.sumMoney DESC,a.investor	LIMIT "+count+" ) v, "
			+ " (SELECT @rowNum := 0) AS rowNum "
			;
		DataSet dataSet = MySQL.executeQuery(conn, sql);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	/**
	 * 
	 * @Title: borrowerGeoDistribution
	
	 * @Description: TODO(借款地域分布)
	
	 * @param conn
	 * @return
	 * @throws SQLException
	 * @throws DataException List<Map<String,Object>>
	 */
	public List<Map<String, Object>>  borrowerGeoDistribution(Connection  conn) throws SQLException, DataException{
		String sql = "SELECT count(*) as provinceProportion, workPro as province FROM v_t_borrow_user_info GROUP BY workPro";
		DataSet dataSet = MySQL.executeQuery(conn, sql);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	/**
	 * 
	 * @Title: totalVolumeByMonth
	
	 * @Description: TODO(单月总借款成交金额)
	
	 * @param conn
	 * @param timeStart
	 * @param timeEnd
	 * @return
	 * @throws SQLException
	 * @throws DataException Map<String,String>
	 */
	public Map<String, String>  totalVolumeByMonth(Connection  conn,String timeStart,String timeEnd) throws SQLException, DataException{
		String sql = "SELECT sum(borrowAmount) AS totalVolume, date_format('" + timeEnd + "', '%Y-%m') AS updateDate FROM t_borrow " +
				"WHERE parentBId = -1 AND borrowStatus IN (3, 4, 5) " + " AND publishTime <= '" + timeEnd + "'";
		DataSet dataSet = MySQL.executeQuery(conn, sql);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	public Map<String, String>  avgLoanFullTimeInSeconds(Connection  conn) throws SQLException, DataException{
		String sql = "SELECT avg(fullBorrowTime - publishTime) as avgTime FROM t_borrow WHERE parentBId = -1 AND borrowStatus IN (3, 4, 5)";
		DataSet dataSet = MySQL.executeQuery(conn, sql);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	public Map<String, String>  avgCreditRightsSoldTimeInSeconds(Connection  conn) throws SQLException, DataException{
		String sql = "SELECT avg(dealTime - publishTime) as avgTime FROM t_assignment_debt WHERE debtStatus = 3";
		DataSet dataSet = MySQL.executeQuery(conn, sql);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	//add by sunbing end
	
	public List<Map<String, Object>>  queryAvailableVoucherList(Connection  conn, long userId,int voucherType) throws SQLException, DataException{
		StringBuffer command = new StringBuffer();
		command.append(" SELECT a.*,datediff(deadTime,curdate()) as remainDays ");
		command.append(" FROM t_gofull_voucher a ");
		command.append(" WHERE a.userId = " + userId);
		command.append(" AND a.usableStatus = 1 ");
		command.append(" AND a.voucherType = " + voucherType);
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	
	
	/**
	 * @Return:
	 * @Descb:查询用户个人余额宝产品总额度
	 * @Throws:
	 */
	public List<Map<String, Object>>  queryYebInvestAmount(Connection  conn,Long userId) throws SQLException, DataException{
		String sql = "SELECT sum(investAmount) money FROM  t_yeb_invest WHERE userId = "+userId+"  AND status < 5 ";
		DataSet dataSet = MySQL.executeQuery(conn, sql);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
}
